﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using Oracle.DataAccess.Client;

namespace Wicresoft.SelfService.DAL
{
    public static class DepartmentHelper
    {
        private static readonly string getdepartment = @"select department from ctm_people_organization where status=1 and company=:company order by department";
        private static readonly string getAllDepartment = @"select distinct(department) from ctm_people_organization where status=1 order by department";

        private static string connectionStrITSM = ConfigurationManager.ConnectionStrings["ITSMContext"].ConnectionString;

        public static List<string> GetListDepartment(string company)
        {
            List<string> departmentlist = new List<string>();
            using (OracleConnection conITSM = new OracleConnection(connectionStrITSM))
            {
                conITSM.Open();
                OracleCommand commandITSM;
                commandITSM = conITSM.CreateCommand();
                commandITSM.CommandType = System.Data.CommandType.Text;
                commandITSM.BindByName = true;
                commandITSM.CommandText = getdepartment;
                OracleParameter companyITSM = new OracleParameter(":company", OracleDbType.NVarchar2, 100, company, System.Data.ParameterDirection.Input);
                commandITSM.Parameters.Add(companyITSM);

                var reader = commandITSM.ExecuteReader();
                while (reader.Read())
                {
                    departmentlist.Add((string)reader["department"]);
                }
            }
            return departmentlist;
        }

        public static List<string> GetAllDepartment()
        {
            List<string> departmentlist = new List<string>();
            using (OracleConnection conITSM = new OracleConnection(connectionStrITSM))
            {
                conITSM.Open();
                OracleCommand commandITSM;
                commandITSM = conITSM.CreateCommand();
                commandITSM.CommandText = getAllDepartment;

                var reader = commandITSM.ExecuteReader();
                while (reader.Read())
                {
                    departmentlist.Add((string)reader["department"]);
                }
            }
            return departmentlist;
        }
    }
}
